from pymysql.cursors import DictCursor
import pymysql
from dbutils.pooled_db import PooledDB
from flask import flash

POOL = PooledDB(
    creator=pymysql,
    maxconnections=20,
    mincached=2,
    blocking=True,
    host="127.0.0.1", port=3306, user="root", passwd="root", charset="utf8", db='design'
)


def get_pool():
    return POOL


def get_db_user(username, password):
    """ 根据token去数据库获取用户信息 """

    # 连接MySQL，去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor(cursor=DictCursor)

    # 1.查询
    cursor.execute("select * from userinfo where username=%s and password=%s", [username, password])
    row_dict = cursor.fetchone()

    # 归还到连接池
    conn.close()
    return row_dict


def insert_db_user(username, password, email):
    """ 根据token去数据库获取用户信息 """

    # 连接MySQL，去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor(cursor=DictCursor)

    affected_rows = cursor.execute("insert into userinfo(username,password,email) values(%s,%s,%s)",
                                   [username, password, email])
    conn.commit()

    # 归还到连接池
    cursor.close()
    conn.close()
    return affected_rows


def insert_db_parameter(start, end):
    # 连接MySQL，去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor(cursor=DictCursor)

    parameter_row = cursor.execute("insert into parameter(start,end) values(%s,%s)", [start, end])
    conn.commit()

    # 归还到连接池
    cursor.close()
    conn.close()
    return parameter_row


def get_db_parameter(per_page_count, offset):
    # 连接MySQL，去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor(cursor=DictCursor)

    # 1.查询
    cursor.execute("select * from parameter order by id desc limit %s offset %s", [per_page_count, offset])
    parameter_dict = cursor.fetchall()

    # 归还到连接池
    cursor.close()
    conn.close()
    return parameter_dict


def insert_db_feedback(userName, userEmail, feedbackType, feedbackContent):
    conn = POOL.connection()
    # 插入数据库
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO feedback (userName, userEmail, feedbackType, feedbackContent) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (userName, userEmail, feedbackType, feedbackContent))
        conn.commit()
    except Exception as e:
        print(e)
        flash('提交反馈时发生错误。')
    else:
        flash('感谢您的反馈！')


def insert_db_user_history_records(userinfo_id, file_name, result_data, image_path, population_size, n_generations,
                                   max_capacity, mutation_rate):
    conn = POOL.connection()
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO history_records (userinfo_id, file_name, result_data, image_path,population_size,n_generations,max_capacity,mutation_rate) VALUES (%s, %s, %s, %s,%s, %s, %s, %s)"
            cursor.execute(sql, (
            userinfo_id, file_name, result_data, image_path, population_size, n_generations, max_capacity,
            mutation_rate))
            conn.commit()
    finally:
        conn.close()


def get_db_user_history_records(userinfo_id, per_page_count, offset):
    # 连接MySQL，去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor(cursor=DictCursor)

    # 1.查询
    cursor.execute("SELECT * FROM history_records WHERE userinfo_id=%s ORDER BY upload_time DESC limit %s offset %s",
                   [userinfo_id, per_page_count, offset])
    records_dict = cursor.fetchall()

    # 归还到连接池
    cursor.close()
    conn.close()
    return records_dict
